package org.databandtech.logmock;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.Instant;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

import org.databandtech.common.Mock;
import org.databandtech.logmock.entity.Person;

/** 建表语句
CREATE TABLE `databand_testtable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `v1` int(10) DEFAULT NULL,
  `v2` int(11) DEFAULT NULL,
  `update_time` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22003 DEFAULT CHARSET=utf8mb4;
**/

public class MySQLMock {
	
	static final String CONNSTR = "jdbc:mysql://localhost:3307/databand?useUnicode=true&characterEncoding=utf-8&useSSL=false";
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";//8.0+ "com.mysql.cj.jdbc.Driver"
	static final String DBUSER = "root";
	static final String DBPASSWD = "mysql";
	static final String TABLE = "databand_testtable";
	static final String COLUMNS = "name,age,address,phone,v1,update_time";  //数据库字段
	static final String MAPENTITY = "Person";  //对应的实体类
	static final int COUNT = 10;
	static final int AGE_LOW = 20;
	static final int AGE_HIGH = 100;
	
	public static void main(String[] args) throws Exception {
		
		List<Person> persons = new ArrayList<Person>();
		for(int i=0;i<COUNT;i++) {
			Person p = new Person();
			p.setAddress(Mock.getRoad());
			p.setAge(Mock.getNum(AGE_LOW,AGE_HIGH));
			int sex = Mock.getNum(0, 1);
			p.setName(Mock.getChineseName());
			p.setPhone(Mock.getTel());
			p.setSex(sex);
			p.setDate(LocalDate.of(2020, Mock.getNum(1, 10), Mock.getNum(1, 30)).toString());
			p.setSex(sex);
			persons.add(p);
		}
		
		Connection conn = null;
        PreparedStatement pstm;
        try{
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
        
            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(CONNSTR,DBUSER,DBPASSWD);
        
            conn.setAutoCommit(false);
            int columnCounts =COLUMNS.split(",").length;
            //组装问号?占位符
            String marks="";
            for (int i=0;i<columnCounts;i++) {
            	if (i==columnCounts-1)
            		marks+="?";
            	else
            		marks+="?,";	
            }
            pstm = conn.prepareStatement("INSERT INTO "+TABLE+"("+COLUMNS+") VALUES("+marks+")");

            for (Person p : persons) {
                pstm.setString(1, p.getName());
                pstm.setInt(2, p.getAge());
                pstm.setString(3, p.getAddress());
                pstm.setString(4, p.getPhone());
                pstm.setInt(5, Mock.getNum(20, 100));
                pstm.setString(6, p.getDate());
                pstm.addBatch();
            }

            int[] counts = pstm.executeBatch(); //执行Batch中的全部语句
            conn.commit();                      //提交到数据库
            conn.setAutoCommit(true);   //在完成批量操作后恢复默认的自动提交方式，
            
            System.out.println("执行行数：."+counts.length);           
            pstm.close();
            conn.close();
        }catch(SQLException se){
            se.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
		
	}
}
